#!/usr/bin/perl -w
use strict;
use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
use db_parameters;




#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#       Create domains tables

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
print STDERR "\tConstructing per domain tables...\n";
print <<'PL/SQLCMD';
    -- Table: interpro_data
    CREATE TABLE domains.interpro_data
    (
        interpro_ac             TEXT,
        interpro_name           TEXT,
        interpro_description    TEXT
    );
    GRANT SELECT ON domains.interpro_data  TO GROUP select_panda;
    CREATE INDEX i__IPRODATA ON domains.interpro_data(interpro_ac);
PL/SQLCMD


#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#       Create median function

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888


{
print STDERR "\tConstructing SQL utility functions...\n";
print <<'PL/SQLCMD';
    CREATE OR REPLACE FUNCTION less(anyelement, anyelement) RETURNS anyelement
        AS 'SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END;' LANGUAGE SQL;
    CREATE OR REPLACE FUNCTION more(anyelement, anyelement) RETURNS anyelement
        AS 'SELECT CASE WHEN $1 > $2 THEN $1 ELSE $2 END;' LANGUAGE SQL;

	--ignore this if it creates an error
	--DROP AGGREGATE bitwise_or (INTEGER);
	CREATE AGGREGATE bitwise_or
	(
		BASETYPE = INTEGER,
		SFUNC = int4or,
		STYPE = INTEGER,
		INITCOND = 0
	);

    -- GROUP_CONCAT()
    -- Note: For DISTINCT and ORDER BY a subquery is required
    CREATE OR REPLACE FUNCTION _group_concat(text, text)
    RETURNS text AS $$
      SELECT CASE
        WHEN $2 IS NULL THEN $1
        WHEN $1 IS NULL THEN $2
        ELSE $1 || $2
      END
    $$ IMMUTABLE LANGUAGE SQL;
    
    CREATE AGGREGATE group_concat (
    	BASETYPE = text,
    	SFUNC = _group_concat,
    	STYPE = text
    );


    CREATE OR REPLACE FUNCTION median (TEXT, TEXT) RETURNS FLOAT8 AS 
    $$
        DECLARE
            table_name alias  for $1;
            col_name alias    for $2;
            med_pos           INT4;
            med_count         INT4 := 1;
            table_size        INT4;
            query             TEXT;
            medianRecord      RECORD;
            median_value      FLOAT8;
        BEGIN
            query := 'SELECT count( ' || col_name || ') from ' || table_name || ';';
        
            -- Get size
            FOR medianRecord IN EXECUTE query LOOP
                table_size := medianRecord.COUNT;
            END LOOP;
        
            med_pos = (table_size - 1 ) / 2;
            IF mod(table_size, 2) = 0 THEN
                --An even number, so we want two records
                med_count := 2;
            END IF;
        
            query := 'SELECT avg( ' || col_name || ' ) AS median FROM (SELECT ' || col_name || ' FROM ' || table_name || ' ORDER BY ' || col_name || ' LIMIT ' || med_count || ' OFFSET ' || med_pos || ') AS a;';
    
            -- Get size
            FOR medianRecord IN EXECUTE query LOOP
                median_value := medianRecord.median;
            END LOOP;
    
            RETURN median_value;
            --RETURN query;
        END;
    $$
     LANGUAGE 'plpgsql';
        
    
    CREATE OR REPLACE FUNCTION median_final (FLOAT8[]) RETURNS FLOAT8 AS 
    $$
        DECLARE
            median_array alias for $1;
            iter           INT4 := 0;
            iter_val       FLOAT8 := -1;
            med_pos        INT4;
            med_count      INT4 := 1;
            median         FLOAT8;
            query          TEXT;
            table_name      TEXT;
            recordCount    INT4 := 1;
            medianRecord   RECORD;
        BEGIN
            
            IF array_upper(median_array, 1) = 0 THEN
                RETURN NULL;
            END IF;
            med_pos :=array_upper(median_array, 1);
            IF med_pos IS NULL THEN
                RETURN NULL;
            END IF;
        
            -- create random table name
            table_name := 'myMedian' || btrim(to_char(floor(random()*10000000),'00000000'),' ') ;
    
            query := 'CREATE TEMPORARY TABLE ' || table_name || ' (val FLOAT8);';
            EXECUTE query;
    
            -- get position of median
            med_pos = (array_upper(median_array, 1) - 1 ) / 2;
            IF mod(array_upper(median_array, 1), 2) = 0 THEN
                --An even number, so we want two records
                med_count := 2;
            END IF;
    
            -- insert values
            FOR iter IN 1..array_upper(median_array, 1) LOOP
                iter_val := median_array[iter];
                query := 'INSERT INTO ' || table_name || 
                            ' (val) VALUES (''' || iter_val || ''');';
                EXECUTE query;
            END LOOP;
    
            -- create index
            query := 'CREATE INDEX i_' || table_name || ' on ' || table_name || ' (val);';
            EXECUTE query;
    
    
            -- Get the result out of query
            query := 'SELECT avg( val ) AS median FROM (SELECT val FROM ' || 
                        table_name || ' ORDER BY val LIMIT ' || 
                        med_count || ' OFFSET ' || med_pos || ') AS a;';
            FOR medianRecord IN EXECUTE query LOOP
                median := medianRecord.median;
            END LOOP;
    
            
            -- play nice and drop the temporary table
            query := 'DROP TABLE ' || table_name || ';';
            EXECUTE query;
    
            RETURN median;
            
        END;
    $$
        LANGUAGE 'plpgsql';
    
    --ignore this if it creates an error
    --DROP AGGREGATE median (real);
    CREATE AGGREGATE median
    (
        BASETYPE = FLOAT8,
        SFUNC = array_append,
        STYPE = FLOAT8[],
        FINALFUNC = median_final,
        INITCOND = '{}'
    );


    --DROP AGGREGATE percentile (t_percentile_helper);
    --DROP TYPE  t_percentile_helper CASCADE;


    CREATE type t_percentile_helper AS 
    (
        val   float8, 
        prcnt float8
    );



    CREATE OR REPLACE FUNCTION percentile_append(FLOAT8[], t_percentile_helper) RETURNS FLOAT8[] AS 
    $$
        DECLARE
            percentile_array  alias for $1;
            percentile_helper alias for $2;
            array_size     int4 := 0;
        BEGIN
            array_size := array_upper(percentile_array, 1);
            IF array_size = 0 OR array_size IS NULL THEN
                IF percentile_helper.val IS NULL THEN
                    RETURN ARRAY[percentile_helper.prcnt];
                ELSE
                    RETURN ARRAY[percentile_helper.prcnt, percentile_helper.val];
                END IF;
            END IF;
            IF NOT percentile_helper.val IS NULL THEN
                RETURN array_append(percentile_array, percentile_helper.val);
            END IF;

            RETURN percentile_array;
        END;
    $$
    LANGUAGE 'plpgsql';

    CREATE OR REPLACE FUNCTION percentile_final (FLOAT8[]) RETURNS FLOAT8 AS 
    $$
        DECLARE
            percentile_array alias for $1;
            iter           INT4 := 0;
            iter_val       FLOAT8 := -1;
            med_pos        INT4 := 0;
            med_count      INT4 := 1;
            result_percentile     FLOAT8;
            query          TEXT;
            table_name      TEXT;
            percentileRecord   RECORD;
            prcnt          FLOAT8;
            array_size     int4 := 0;
        BEGIN

            -- get array size
            --first value in array is the specified percentile
            --ugly hack to get around postgres' lack of multiple input parameters
            IF array_upper(percentile_array, 1) IS NULL THEN
                RETURN NULL;
            END IF;
            prcnt := percentile_array[1];
            array_size := array_upper(percentile_array, 1) - 1;
            
            --only percentile is specied, all values null
            IF array_size <= 1 THEN
                RETURN NULL;
            END IF;
            

        
            -- create random table name
            table_name := 'mypercentile' || btrim(to_char(floor(random()*10000000),'00000000'),' ') ;
    
            query := 'CREATE TEMPORARY TABLE ' || table_name || ' (val FLOAT8);';
            EXECUTE query;
    
            -- get position of percentile
            med_pos = cast(trunc((array_size - 1 ) * prcnt) AS INTEGER);
            IF (array_size - 1 ) * prcnt - med_pos > 0.4 THEN
                --An even number, so we want two records
                med_count := 2;
            END IF;
            
            -- insert values
            FOR iter IN 2..array_size + 1 LOOP
                iter_val := percentile_array[iter];
                query := 'INSERT INTO ' || table_name || 
                            ' (val) VALUES (''' || iter_val || ''');';
                EXECUTE query;
            END LOOP;
    
            -- create index
            query := 'CREATE INDEX i_' || table_name || ' on ' || table_name || ' (val);';
            EXECUTE query;
    
    
            -- Get the result out of query
            query := 'SELECT avg( val ) AS percentile FROM (SELECT val FROM ' || 
                        table_name || ' ORDER BY val LIMIT ' || 
                        med_count || ' OFFSET ' || med_pos || ') AS a;';
            FOR percentileRecord IN EXECUTE query LOOP
                result_percentile := percentileRecord.percentile;
            END LOOP;
    
            
            -- play nice and drop the temporary table
            query := 'DROP TABLE ' || table_name || ';';
            EXECUTE query;
    
            RETURN result_percentile;
            
        END;
    $$
        LANGUAGE 'plpgsql';
    

    CREATE AGGREGATE percentile
    (
        BASETYPE = t_percentile_helper,
        SFUNC = percentile_append,
        STYPE = FLOAT8[],
        FINALFUNC = percentile_final,
        INITCOND = '{}'
    );    
PL/SQLCMD
    
    

















#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#       Create cDNA table, ENSEMBL IDs and batch tables

#           in public (taxon agnostic) space for easy use in X-species comparisons

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
    
print STDERR "\tConstructing cDNA and ens_id tables...\n";
print <<'PL/SQLCMD';
------------------------------------------------------------------------------------------
-- tables for cDNA, Ensembl IDs
------------------------------------------------------------------------------------------
-- Table: public.cdna_sequences
    CREATE TABLE public.cdna_sequences (
        pi             OID,
        pi_reserved    OID,
        seq_hash       OID[],
        prot_id        TEXT,
        cdna           TEXT,
        cdna_hash      TEXT
    ) ;
    GRANT SELECT ON public.cdna_sequences      TO GROUP select_panda;
    CREATE INDEX i__cdna_sequences1 ON public.cdna_sequences(pi, prot_id);
    CREATE INDEX i__cdna_sequences2 ON public.cdna_sequences(prot_id);
    COMMENT ON TABLE cdna_sequences IS 'cDNA sequences';


    ------------------------------------------------------------------------------------------
    --  public.ens_id
    ------------------------------------------------------------------------------------------
    CREATE TABLE public.ens_id (
        pi              OID,
        gene_id         TEXT,
        prot_id        TEXT,
        transcript_id   TEXT,
        species		    TEXT
    ) ;
    GRANT SELECT ON ens_id              TO GROUP select_panda;
    -- --------------------------------------------------------
    -- indices
    --
    CREATE INDEX i__ens_id_prot_id ON ens_id(prot_id);
    CREATE INDEX i__ens_id ON ens_id(pi, prot_id);
    CREATE INDEX i__ens_id_gi ON ens_id(gene_id);
    COMMENT ON TABLE ens_id IS 'Identifier map';




    -- --------------------------------------------------------
    -- GO_term_descriptions
    --
    CREATE TABLE public.GO_term_descriptions
    (
        GO_id       INTEGER,
        GO_category text,
        description text
    );
    GRANT SELECT ON GO_term_descriptions TO GROUP select_panda;
    CREATE INDEX i__godesc_go_id ON GO_term_descriptions(GO_id);
    COMMENT ON TABLE GO_term_descriptions IS 'Gene Ontology terms in full';
    

    
    ------------------------------------------------------------------------------------------
    -- tables for orthologs
    ------------------------------------------------------------------------------------------
    -- Table: orthologs.protocols
    CREATE TABLE orthologs.protocols
    (
        name            TEXT,
        protocol_id     INT4
    ) ;
    COMMENT ON TABLE orthologs.protocols IS 'Protocols used to predict orthology relationships';
    GRANT SELECT ON orthologs.protocols      TO GROUP select_panda;
    CREATE SEQUENCE s_protocols START 1;

    
    -- Table orthologs.predicted_type
    CREATE TABLE orthologs.predicted_type
    (
        gene_id              TEXT,
        disentangled_gene_id TEXT,
        species              TEXT,
        protocol_id          OID,
        gene_type            TEXT
    );
    GRANT SELECT ON orthologs.predicted_type      TO GROUP select_panda;
    CREATE INDEX i__orth_predtype1 ON orthologs.predicted_type(protocol_id, gene_id);
    CREATE INDEX i__orth_predtype2 ON orthologs.predicted_type(protocol_id, disentangled_gene_id);
    COMMENT ON TABLE orthologs.predicted_type IS 'The gene type (e.g. ortholog, pseudo) predicted by the pipeline';


    -- Table orthologs.predicted_type
    CREATE TABLE orthologs.representative_transcripts
    (
        gene_id              TEXT,
        disentangled_gene_id TEXT,
        species              TEXT,
        protocol_id          OID,
        prot_id              TEXT
    );
    GRANT SELECT ON orthologs.representative_transcripts      TO GROUP select_panda;
    CREATE INDEX i__orth_reptrans ON orthologs.representative_transcripts(protocol_id, gene_id);
    CREATE INDEX i__orth_reptrans2 ON orthologs.representative_transcripts(protocol_id, disentangled_gene_id);
    COMMENT ON TABLE orthologs.representative_transcripts IS 'The representative transcript for that gene predicted by the pipeline';




    -- Table orthologs.align_kaks
    CREATE TABLE orthologs.align_kaks
    (
        gene_id1            TEXT,
        gene_id2            TEXT,
        prot_id1           text,
        prot_id2           text,
        pi1                 OID,
        pi2                 OID,
        dN                  REAL,
        dS                  REAL,
        dnds                REAL,
        count_N             REAL,
        count_S             REAL,
        dn_stderr           REAL,
        ds_stderr           REAL,
        kappa               REAL,
        lnL                 REAL,
        tau                 REAL,
        error_str           TEXT,
        align_start         INT4,
        align_len           INT4,
        aligned_positions   INT4,
        coverage            FLOAT8,
        e_value             FLOAT8,
        len1                INT4,
        len2                INT4,
        aligned_residues    INT4,
        percent_id          FLOAT8,
        bitscore            FLOAT8,
        gaps1               text,
        gaps2               text,
        protocol_id         INT4
    );
    GRANT SELECT ON orthologs.align_kaks      TO GROUP select_panda;
    CREATE INDEX i__orth_alignkaks ON orthologs.align_kaks(gene_id1, gene_id2);
    CREATE INDEX i__orth_alignkaks2 ON orthologs.align_kaks(pi1, prot_id1);
    CREATE INDEX i__orth_alignkaks3 ON orthologs.align_kaks(pi2, prot_id2);
    COMMENT ON TABLE orthologs.align_kaks IS 'dNdS and alignment data between orthologs';


    -- Table orthologs.ortholog_sets
    CREATE TABLE orthologs.ortholog_sets
    (
        prot_id              TEXT,
        gene_id              TEXT,
        disentangled_gene_id TEXT,
        ortholog_id          OID,
        ortholog_type        TEXT,
        species              TEXT,
        dS                   REAL,
        protocol_id          OID
    );
    CREATE INDEX i__ortho_sets ON orthologs.ortholog_sets(prot_id);
    CREATE INDEX i__ortho_sets3 ON orthologs.ortholog_sets(protocol_id, gene_id, species);
    CREATE INDEX i__ortho_sets2 ON orthologs.ortholog_sets(gene_id, ortholog_id, species);
    GRANT SELECT ON orthologs.ortholog_sets      TO GROUP select_panda;
    COMMENT ON TABLE orthologs.ortholog_sets IS 'Orthology predictions for different protocols';

    -- Table orthologs.split_genes
    CREATE TABLE orthologs.split_genes
    (
        gene_id              TEXT,
        disentangled_gene_id TEXT,
        prot_id              TEXT,
        protocol_id          INT4
    );
    CREATE INDEX i__ortho_split ON orthologs.split_genes(protocol_id, gene_id);
    GRANT SELECT ON orthologs.split_genes      TO GROUP select_panda;
    COMMENT ON TABLE orthologs.split_genes IS 'Merged genes split by the phyOP protocol';

    
    -- TABLE orthologs.one_or_many_orthologs
    CREATE TABLE orthologs.one_or_many_orthologs
    (
        protocol_id         INT4 , 
        protocol_id_1s      INT4,
        protocol_id_manys   INT4,
        gene_id             TEXT,
        species             TEXT
    );
    GRANT SELECT ON orthologs.one_or_many_orthologs     TO GROUP select_panda;
    COMMENT ON TABLE orthologs.one_or_many_orthologs IS 'Whether orthology relationships are one or manys';



    
    -- Table orthologs.gene_pseudo_evidence
    CREATE TABLE orthologs.gene_pseudo_evidence
    (
        gene_id              TEXT,
        disentangled_gene_id TEXT,
        reason_id            INT4,
        protocol_id          OID
    );
    GRANT SELECT ON orthologs.gene_pseudo_evidence      TO GROUP select_panda;
    CREATE INDEX i__ortho_gene_pseudo_evidence ON orthologs.gene_pseudo_evidence(protocol_id, gene_id);
    COMMENT ON TABLE orthologs.gene_pseudo_evidence IS 'Component evidence for pseudos: Disruptions or Dispersed && Single exonic';

    -- Table orthologs.pseudo_evidence_names
    CREATE TABLE orthologs.pseudo_evidence_names
    (
        reason text,
        reason_id INT4
    );
    GRANT SELECT ON orthologs.pseudo_evidence_names      TO GROUP select_panda;
    CREATE INDEX i__ortho_pseudo_evidence_names ON orthologs.pseudo_evidence_names(reason_id);
    COMMENT ON TABLE orthologs.pseudo_evidence_names IS 'Names for Pseudo Evidence.';

    -- Table orthologs.pseudo_analysis_summary
    CREATE TABLE orthologs.pseudo_analysis_summary
    (
        protocol_id      OID,
        category         TEXT,
        species          TEXT,
        ortholog_type    TEXT,
        gene_count       INT4
    );
    GRANT SELECT ON orthologs.pseudo_analysis_summary      TO GROUP select_panda;
    CREATE INDEX i__ortho_pseudo_analysis_summary ON orthologs.pseudo_analysis_summary(protocol_id);
    COMMENT ON TABLE orthologs.pseudo_analysis_summary IS 'Summary for Pseudo Analysis.';


    -- Table orthologs.syntenic_distances
    CREATE TABLE orthologs.syntenic_distances
    (
        gene_id                     TEXT,
        closest_neighbour_dist      INT4,
        other_dir_neighbour_dist    INT4,
        protocol_id                 OID 
    );
    GRANT SELECT ON orthologs.syntenic_distances      TO GROUP select_panda;
    CREATE INDEX i__ortho_synteny_dist ON orthologs.syntenic_distances(protocol_id, gene_id) WHERE closest_neighbour_dist > 50;
    COMMENT ON TABLE orthologs.syntenic_distances IS 'Distance to back-ortholog of neighbour of ortholog';


    -- Table orthologs.micro_syntenic_blocks
    CREATE TABLE orthologs.micro_syntenic_blocks
    (
        micro_syntenic_block_id INT4,
        gene_id                 TEXT,
        disentangled_gene_id    TEXT,
        species                 TEXT,
        protocol_id             OID,
        analysis_set            TEXT
    );
    GRANT SELECT ON orthologs.micro_syntenic_blocks      TO GROUP select_panda;
    COMMENT ON TABLE orthologs.micro_syntenic_blocks IS 'Runs of adjacent orthologs in the same strand orientation';

    -- Table orthologs.micro_syntenic_blocks
    CREATE TABLE orthologs.micro_syntenic_block_sizes
    (
        micro_syntenic_block_id INT4,
        species                 TEXT,
        protocol_id             OID,
        block_size              INT4,
        analysis_set            TEXT
    );
    GRANT SELECT ON orthologs.micro_syntenic_block_sizes      TO GROUP select_panda;
    COMMENT ON TABLE orthologs.micro_syntenic_block_sizes IS 'The size of each micro syntenic block';

    -- Table orthologs.micro_syntenic_block_medians
    CREATE TABLE orthologs.micro_syntenic_block_size_summary
    (
        species                 TEXT,
        protocol_id             OID,
        protocol_name           TEXT,
        median_block_size       INT4,
        analysis_set            TEXT
    );
    GRANT SELECT ON orthologs.micro_syntenic_block_size_summary      TO GROUP select_panda;
    COMMENT ON TABLE orthologs.micro_syntenic_block_size_summary IS 'The half or more of genes are in N50 size of micro-syntenic blocks';

    -- Table orthologs.micro_syntenic_blocks
    CREATE TABLE orthologs.micro_syntenic_block_size_histograms
    (
        block_size              INT4,
        count                   INT4,        
        species                 TEXT,
        protocol_id             OID,
        cummulative_count       INT4,
        analysis_set            TEXT
    );
    GRANT SELECT ON orthologs.micro_syntenic_block_size_histograms      TO GROUP select_panda;
    COMMENT ON TABLE orthologs.micro_syntenic_block_size_histograms IS 'Histograms of micro-syntenic blocks sizes';

    -- Table orthologs.macro_syntenic_block_size_histograms
    CREATE TABLE orthologs.macro_syntenic_block_size_histograms
    (
        block_size                  INT4,
        cummulative_gene_fraction   REAL,
        count                       INT4,        
        species                     TEXT,
        protocol_id                 OID,
        analysis_set                TEXT
    );
    GRANT SELECT ON orthologs.macro_syntenic_block_size_histograms      TO GROUP select_panda;
    COMMENT ON TABLE orthologs.macro_syntenic_block_size_histograms IS 'Histograms of macro-syntenic blocks sizes';


    -- TABLE orthologs.pairwise
    CREATE TABLE orthologs.pairwise
    (
        ortholog_type  TEXT, 
        protocol_id    INT4, 
        ortholog_id    OID , 
        species1       TEXT, 
        gene_id1       TEXT, 
        species2       TEXT, 
        gene_id2       TEXT 
    );
    GRANT SELECT ON orthologs.pairwise     TO GROUP select_panda;
    COMMENT ON TABLE orthologs.pairwise IS 'All pairwise orthology relationships';
    CREATE INDEX i__ortho_pairwise1 ON orthologs.pairwise(protocol_id, gene_id1, gene_id2, species1, species2);

    -- TABLE orthologs.pairwise_prot_id
    CREATE TABLE orthologs.pairwise_prot_id
    (
        ortholog_type   TEXT, 
        protocol_id     INT4, 
        ortholog_id     OID, 
        species1        TEXT, 
        species2        TEXT, 
        gene_id1        TEXT, 
        gene_id2        TEXT,
        prot_id1        TEXT,
        prot_id2        TEXT
    );
    GRANT SELECT ON orthologs.pairwise_prot_id     TO GROUP select_panda;
    COMMENT ON TABLE orthologs.pairwise_prot_id IS 'Pairwise orthology relationships with accession codes';
    CREATE INDEX i__ortho_pairwise1_ac ON orthologs.pairwise_prot_id(gene_id1, gene_id2, prot_id1, prot_id2);


    -- TABLE orthologs.pairwise_overlap
    CREATE TABLE orthologs.pairwise_overlap
    (
        protocol_id         INT4 , 
        protocol_id_1s      INT4,
        protocol_id_manys   INT4,
        species1            TEXT, 
        gene_id1            TEXT, 
        species2            TEXT, 
        gene_id2            TEXT 
    );
    GRANT SELECT ON orthologs.pairwise_overlap     TO GROUP select_panda;
    COMMENT ON TABLE orthologs.pairwise_overlap IS 'Pairwise orthology relationships present in one or more protocols';
    CREATE INDEX i__ortho_pairwiseover ON orthologs.pairwise_overlap(protocol_id, gene_id1, gene_id2, species1, species2);

    -- TABLE orthologs.resolved_orthologs
    CREATE TABLE orthologs.resolved_orthologs
    (
        prot_id         TEXT,
        method          TEXT,
        protocol_id     INT4

    );
    GRANT SELECT ON orthologs.resolved_orthologs     TO GROUP select_panda;
    COMMENT ON TABLE orthologs.resolved_orthologs IS 'How phylop orthologues were resolved';
    CREATE INDEX i__ortho_resolved ON orthologs.resolved_orthologs(prot_id);

    -- TABLE orthologs.resolved_orthologs
    CREATE OR REPLACE VIEW orthologs.resolved_orthologs_summary
        AS 
        SELECT 
                protocol_id, 
                ortholog_type, 
                method, 
                species, 
                COUNT(*) 
            FROM 
                orthologs.ortholog_sets NATURAL JOIN 
                resolved_orthologs 
            GROUP BY 
                method, 
                ortholog_type, 
                species, 
                protocol_id 
            ORDER BY 
                protocol_id, 
                ortholog_type, 
                LENGTH(method) DESC, 
                species;
    GRANT SELECT ON orthologs.resolved_orthologs_summary      TO GROUP select_panda;
    COMMENT ON VIEW orthologs.resolved_orthologs_summary IS 'How ortholog gene relationships are translated from transcript phylogeny';




    -- --------------------------------------------------------
    -- significant_GO_terms
    --
    CREATE TABLE orthologs.significant_GO_terms
    (
        over_representation BOOLEAN,
        category            TEXT,
        GO_id               INTEGER,
        protocol_id         OID,
        significance        FLOAT8,
        species             TEXT,
        GO_slim             BOOLEAN
    );
    GRANT SELECT ON orthologs.significant_GO_terms TO GROUP select_panda;
    CREATE INDEX i__ortho_signgo ON orthologs.significant_GO_terms(protocol_id, category, GO_id) WHERE over_representation = TRUE;



    CREATE TABLE orthologs.pairwise_overlap_prot_id
    (
        protocol_id         INT4, 
        protocol_id_1s      INT4,
        protocol_id_manys   INT4,
        species1            TEXT, 
        species2            TEXT, 
        prot_id1           TEXT,
        prot_id2           TEXT
    );
    GRANT SELECT ON orthologs.pairwise_overlap_prot_id     TO GROUP select_panda;
    COMMENT ON TABLE orthologs.pairwise_overlap_prot_id IS 'Pairwise orthology relationships with accession codes present in one or more protocols';
    CREATE INDEX i__ortho_pairwiseover_prot_id ON orthologs.pairwise_overlap_prot_id(prot_id1, prot_id2, protocol_id, species1, species2);



    CREATE TABLE orthologs.closest_other_species_prot_id
    (
        prot_id                 TEXT, 
        other_species_prot_id   TEXT, 
        dS                      REAL,
        protocol_id             INT4

    );
    GRANT SELECT ON orthologs.closest_other_species_prot_id     TO GROUP select_panda;
    COMMENT ON TABLE orthologs.closest_other_species_prot_id IS 'The protein in another species with the smallest dS';
    CREATE INDEX i__ortho_closestacc ON orthologs.closest_other_species_prot_id(prot_id);
    
    CREATE TABLE orthologs.closest_other_species
    (
        gene_id                 TEXT, 
        prot_id                 TEXT, 
        species                 TEXT,
        other_species_gene_id   TEXT, 
        other_species_prot_id   TEXT, 
        other_species           TEXT,
        dS                      REAL,
        protocol_id             INT4
    );
    GRANT SELECT ON orthologs.closest_other_species     TO GROUP select_panda;
    COMMENT ON TABLE orthologs.closest_other_species IS 'The gene in another species with the smallest dS';
    CREATE INDEX i__ortho_closest ON orthologs.closest_other_species(gene_id, species);

    CREATE TABLE orthologs.closest_same_species
    (
        gene_id                 TEXT, 
        species                 TEXT,
        same_species_gene_id    TEXT, 
        dS                      REAL,
        protocol_id             INT4

    );
    GRANT SELECT ON orthologs.closest_same_species     TO GROUP select_panda;
    COMMENT ON TABLE orthologs.closest_same_species IS 'The gene in the same species with the smallest dS';
    CREATE INDEX i__ortho_closestp ON orthologs.closest_same_species(gene_id, species);


    CREATE TABLE orthologs.ortholog_exons
    (
        gene_id1            TEXT, 
        gene_id2            TEXT
    );
    GRANT SELECT ON orthologs.ortholog_exons     TO GROUP select_panda;
    COMMENT ON TABLE orthologs.ortholog_exons IS 'The gene pairs with orthologous exons';
    CREATE INDEX i__ortho_ex ON orthologs.ortholog_exons(gene_id1, gene_id2);

    CREATE TABLE orthologs.genes_with_ortholog_exons
    (
        protocol_id         INT4,
        gene_id             TEXT
    );
    GRANT SELECT ON orthologs.genes_with_ortholog_exons     TO GROUP select_panda;
    COMMENT ON TABLE orthologs.genes_with_ortholog_exons IS 'Genes with any ortholog with matching exons ';
    CREATE INDEX i__ortho_gene_ex ON orthologs.genes_with_ortholog_exons(gene_id);

    -- View orthologs.pseudo_genes
    CREATE OR REPLACE VIEW orthologs.pseudo_genes
        AS 
        SELECT
                gene_id, 
                disentangled_gene_id,
                species,
                protocol_id 
            FROM
                orthologs.predicted_type
            WHERE
                gene_type ~ '^pseudo';
    GRANT SELECT ON orthologs.pseudo_genes      TO GROUP select_panda;
    COMMENT ON VIEW orthologs.pseudo_genes IS 'Multi-disrupted || (Dispersed && (1 exon || Disrupted) ), with orphans';


    -- View orthologs.real_genes
    CREATE OR REPLACE VIEW orthologs.real_genes
        AS 
        SELECT
                gene_id, 
                disentangled_gene_id,
                species,
                protocol_id 
            FROM
                orthologs.predicted_type
            WHERE
                gene_type <> 'pseudo';
    GRANT SELECT ON orthologs.real_genes      TO GROUP select_panda;
    COMMENT ON VIEW orthologs.real_genes IS 'Not pseudos, includes orphans';


    -- View orthologs.orphans
    CREATE OR REPLACE VIEW orthologs.orphans
        AS 
        SELECT
                gene_id, 
                disentangled_gene_id,
                species,
                protocol_id 
            FROM
                orthologs.predicted_type
            WHERE
                gene_type ~ '^Orphan.*homolog';
    GRANT SELECT ON orthologs.orphans      TO GROUP select_panda;
    COMMENT ON VIEW orthologs.orphans IS 'All orphaned genes (not including split orphans)';

    CREATE OR REPLACE VIEW orthologs.predicted_type_summary
        AS 
        SELECT
            species, 
            gene_type, 
            protocol_id,
            COUNT (*)
            FROM
                predicted_type
            GROUP BY
                protocol_id,
                species, 
                gene_type 
            ORDER BY
                protocol_id,
                species, 
                gene_type;
    GRANT SELECT ON orthologs.predicted_type_summary     TO GROUP select_panda;
    COMMENT ON VIEW orthologs.predicted_type_summary IS 'Counts of the various gene types (e.g. ortholog, pseudo) predicted by the pipeline';


    CREATE OR REPLACE VIEW orthologs.genes_with_ortholog_exons_summary
        AS 
        SELECT
            species, 
            gene_type, 
            protocol_id,
            COUNT (*)
            FROM
                predicted_type NATURAL JOIN
                genes_with_ortholog_exons
            GROUP BY
                protocol_id,
                species, 
                gene_type 
            ORDER BY
                species, 
                gene_type;
    GRANT SELECT ON orthologs.genes_with_ortholog_exons_summary     TO GROUP select_panda;
    COMMENT ON VIEW orthologs.genes_with_ortholog_exons_summary IS 'Counts of the various gene types with orthologous exons';



    -- View orthologs.paralog_stats
    CREATE OR REPLACE VIEW orthologs.paralog_stats
        AS 
        SELECT 
                ortholog_id, 
                protocol_id, 
                COUNT(*) AS paralog_count, 
                species 
            FROM 
                orthologs.ortholog_sets 
            WHERE
                ortholog_type ~ 'to' or
                ortholog_type ~ 'paralog'
            GROUP BY 
                species, 
                protocol_id, 
                ortholog_id;
    GRANT SELECT ON orthologs.paralog_stats      TO GROUP select_panda;
    COMMENT ON VIEW orthologs.paralog_stats IS 'Stats for Members of ortholog group of this species';




    ------------------------------------------------------------------------------------------
    --  local_paralogs
    ------------------------------------------------------------------------------------------
    CREATE TABLE orthologs.local_paralogs
    (
        gene_id              TEXT,
        disentangled_gene_id TEXT,
        ortholog_id          INT4,
        local_cluster_id     INT4,
        protocol_id          INT4
    );
    GRANT SELECT ON orthologs.local_paralogs   TO GROUP select_panda;
    CREATE INDEX i__ortho_local_para ON orthologs.local_paralogs(protocol_id, gene_id);
    COMMENT ON TABLE orthologs.local_paralogs IS 'Members of ortholog group which are adjacent';



--    CREATE TABLE orthologs.phylop_genes
--    (
--        gene_id         TEXT, 
--        species         TEXT, 
--        status          TEXT
--    );
--    CREATE INDEX i__ortho_phylopg ON orthologs.phylop_genes(gene_id, species);
--    GRANT SELECT ON orthologs.phylop_genes     TO GROUP select_panda;

PL/SQLCMD
}




#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#       Create per taxon tables

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
{
print STDERR "\tConstructing per taxon tables...\n";
print <<'PL/SQLCMD';

    -- tables for taxon
    ------------------------------------------------------------------------------------------
    SELECT '\tCreate tables unique to taxon';



    ------------------------------------------------------------------------------------------
    --  prot_exons
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.prot_exons (
        prot_id        TEXT,
        exon_id        TEXT,
        start          INT4,
        finish         INT4
    ) ;
    GRANT SELECT ON taxon.prot_exons           TO GROUP select_panda;
    CREATE INDEX i__taxon_prot_exons ON taxon.prot_exons( prot_id, start);
    CREATE INDEX i__taxon_prot_exons2 ON taxon.prot_exons( exon_id, prot_id);
    COMMENT ON TABLE taxon.prot_exons IS 'Which exons and loci belong to which protein';

    ------------------------------------------------------------------------------------------
    --  prot_coding_exons
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.prot_coding_exons (
        prot_id         TEXT,
        exon_id         TEXT,
        coding_offset   INT4,
        length          INT4,
        rank            INTEGER,
        frame           INTEGER
    ) ;
    GRANT SELECT ON taxon.prot_coding_exons           TO GROUP select_panda;
    CREATE INDEX i__taxon_prot_cexons ON taxon.prot_coding_exons( prot_id, rank);
    COMMENT ON TABLE taxon.prot_coding_exons IS 'Which coding exons belong to which protein';

    ------------------------------------------------------------------------------------------
    --  prot_coding_introns
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.prot_coding_introns (
        prot_id         TEXT,
        start           INTEGER,
        finish          INTEGER,
        length          INTEGER,
        rank            INTEGER
    ) ;
    GRANT SELECT ON taxon.prot_coding_introns           TO GROUP select_panda;
    CREATE INDEX i__taxon_prot_cintrons ON taxon.prot_coding_introns( prot_id, rank);
    COMMENT ON TABLE taxon.prot_coding_introns IS 'Which introns belong to which protein';


    ------------------------------------------------------------------------------------------
    --  prot_coding_structures
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.prot_coding_structures (
        prot_id        TEXT,
        coding_struct   TEXT,
        start           INT4,
        finish          INT4,
        mapping_gaps1   TEXT, 
        mapping_gaps2   TEXT,
        exon_boundaries TEXT
    ) ;
    GRANT SELECT ON taxon.prot_coding_structures           TO GROUP select_panda;
    CREATE INDEX i__taxon_prot_coding_structures ON taxon.prot_coding_structures( prot_id);
    COMMENT ON TABLE taxon.prot_coding_structures IS 'Coding sequence mapped onto gene exonic structure';

    ------------------------------------------------------------------------------------------
    --  prot_exon_stats
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.prot_exon_stats (
        prot_id           TEXT,
        intron_min_size   INT4,
        intron_max_size   INT4,
        exon_count        INT4,
        transcript_len    INT4,
        real_exon_count   INT4,
        disruptions_count INT4
    ) ;
    GRANT SELECT ON taxon.prot_exon_stats           TO GROUP select_panda;
    CREATE INDEX i__taxon_prot_exon_stats ON taxon.prot_exon_stats(prot_id);
    COMMENT ON TABLE taxon.prot_exon_stats IS 'Protein (incl. non-coding) struct: intron, disruptions, transcript length';



    ------------------------------------------------------------------------------------------
    --  prot_exon_sequences
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.prot_exon_sequences (
        prot_id             TEXT,
        exonic_dna_sequence TEXT
    ) ;
    GRANT SELECT ON taxon.prot_exon_sequences TO GROUP select_panda;
    CREATE INDEX i__taxon_prot_exon_seq ON taxon.prot_exon_sequences(prot_id);
    COMMENT ON TABLE taxon.prot_exon_sequences IS 'DNA from concatenated exon sequences for each transcript';


    ------------------------------------------------------------------------------------------
    --  taxon.prot_interpro_matches
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.prot_interpro_matches (
        prot_id         TEXT,
        method          TEXT,
        match_ac        VARCHAR(25),
        interpro_ac     VARCHAR(9),
        pos_from        INT4,
        pos_to          INT4,
        e_value         FLOAT8
    );
    GRANT SELECT ON taxon.prot_interpro_matches    TO GROUP select_panda;
    CREATE INDEX i__taxon_ipro__acc ON taxon.prot_interpro_matches(prot_id);
    COMMENT ON TABLE taxon.prot_interpro_matches IS 'Interpro matches per transcript (SMART PFAM)';


    ------------------------------------------------------------------------------------------
    --  taxon.prot_disruptions
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.prot_disruptions (
        prot_id           TEXT,
        disruptions_count INT4
    ) ;
    GRANT SELECT ON taxon.prot_disruptions           TO GROUP select_panda;
    CREATE INDEX i__taxon_prot_disruptions ON taxon.prot_disruptions(prot_id);
    COMMENT ON TABLE taxon.prot_disruptions IS 'Protein disruptions';


    ------------------------------------------------------------------------------------------
    --  gene_loci
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.gene_loci (
        gene_id         TEXT,
        gene_position   REAL,
        db_origin       TEXT,
        chromosome      TEXT,
        start           INT4,
        finish          INT4,
        strand          BOOLEAN
    ) ;
    GRANT SELECT ON taxon.gene_loci           TO GROUP select_panda;
    CREATE INDEX i__taxon_gene_loci ON taxon.gene_loci(gene_id);
    COMMENT ON TABLE taxon.gene_loci IS 'Gene coordinates';

    ------------------------------------------------------------------------------------------
    --  prot_positions
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.prot_positions (
        prot_id         TEXT,
        prot_position   REAL
    ) ;
    GRANT SELECT ON taxon.prot_positions           TO GROUP select_panda;
    CREATE INDEX i__taxon_prot_positions ON taxon.prot_positions(prot_id);
    COMMENT ON TABLE taxon.prot_positions IS 'Protein positions as sub-positions of gene_loci gene_positions';



    ------------------------------------------------------------------------------------------
    --  gene_descriptions
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.gene_descriptions (
        gene_id         TEXT,
        description     TEXT
    ) ;
    GRANT SELECT ON taxon.gene_descriptions TO GROUP select_panda;
    CREATE INDEX i__taxon_gene_descr_gi ON taxon.gene_descriptions(gene_id);
    COMMENT ON TABLE taxon.gene_descriptions IS 'Gene descriptions from Ensembl';


    ------------------------------------------------------------------------------------------
    --  gene_coding_exon_counts
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.gene_coding_exon_counts
    (
        gene_id         TEXT,
        exon_count      INT4
    ) ;
    GRANT SELECT ON taxon.gene_coding_exon_counts           TO GROUP select_panda;
    CREATE INDEX i__taxon_gene_coding_exon_counts ON taxon.gene_coding_exon_counts(gene_id);
    COMMENT ON TABLE taxon.gene_coding_exon_counts IS 'Coding exon counts per gene';




    CREATE TABLE taxon.gene_longest_transcript 
    (
        gene_id TEXT, 
        prot_id TEXT,
        species TEXT
    );
    GRANT SELECT ON taxon.gene_longest_transcript     TO GROUP select_panda;
    COMMENT ON TABLE taxon.gene_longest_transcript IS 'The longest transcript for each gene';
    CREATE INDEX i__taxon_longest ON taxon.gene_longest_transcript(gene_id);


    -- --------------------------------------------------------
    -- taxon.gene_go_matches
    --
    CREATE TABLE taxon.gene_go_matches
    (
        GO_id       INTEGER,
        gene_id     text,
        species     text
    );
    GRANT SELECT ON taxon.gene_go_matches TO GROUP select_panda;
    CREATE INDEX i__go_gene_id ON gene_go_matches(gene_id, species);
    CREATE INDEX i__go_go_id ON gene_go_matches(GO_id);
    COMMENT ON TABLE taxon.gene_go_matches IS 'Gene Ontology terms per gene';

    -- --------------------------------------------------------
    -- taxon.gene_go_matches
    --
    CREATE TABLE taxon.gene_pseudo_stats 
    (
        gene_id                 TEXT, 
        max_exon_count          INTEGER, 
        min_disruptions_count   INTEGER, 
        dispersed               BOOLEAN, 
        protocol_id             OID
    );
    GRANT SELECT ON taxon.gene_pseudo_stats  TO GROUP select_panda;
    CREATE INDEX i__gene_pseudo_stats ON taxon.gene_pseudo_stats(protocol_id, gene_id);
    COMMENT ON TABLE taxon.gene_pseudo_stats IS 'Whether genes are likely to be retropseudo';

    
    ------------------------------------------------------------------------------------------
    -- tables for chromosomes
    ------------------------------------------------------------------------------------------
    -- Table: taxon.chromosomes
    CREATE TABLE taxon.chromosomes (
        species        TEXT,
        name           TEXT,
        length         INTEGER
    ) ;
    GRANT SELECT ON taxon.chromosomes      TO GROUP select_panda;
    CREATE INDEX i__chromosomes1 ON taxon.chromosomes(species, name);
    COMMENT ON TABLE taxon.chromosomes IS 'Chromosomes and lengths for which we have sequence data';


    ------------------------------------------------------------------------------------------
    --  exon_sequences
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.exon_sequences (
        exon_id      TEXT,
        dna_sequence TEXT
    ) ;
    GRANT SELECT ON taxon.exon_sequences TO GROUP select_panda;
    CREATE INDEX i__taxon_exon_seq ON taxon.exon_sequences(exon_id);
    COMMENT ON TABLE taxon.exon_sequences IS 'DNA sequence for each exon';



    ------------------------------------------------------------------------------------------
    --  assembly_gaps
    ------------------------------------------------------------------------------------------
    CREATE TABLE taxon.assembly_gaps
    (
        species         TEXT,                                  
        chromosome      TEXT,
        start           INT4,
        finish          INT4,
        type            TEXT,
        bridge          TEXT
    );
    GRANT SELECT ON taxon.assembly_gaps   TO GROUP select_panda;
    COMMENT ON TABLE taxon.assembly_gaps IS 'Ensembl data';


PL/SQLCMD

}










